-- @owner: @zhanghuan96
-- @date: 2024/11/20
-- @testpoint: getxmltype函数，查询语句为多表连接查询
@conn PrimaryDbAmode;
--前置条件
drop extension if exists gms_xmlgen;
create extension gms_xmlgen;
set behavior_compat_options = 'bind_procedure_searchpath';
--step1:创建表;expect:成功
drop table if exists t_getxmltype0011;
CREATE TABLE t_getxmltype0011 (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);
drop table if exists t_getxmltype0011_01;
CREATE TABLE t_getxmltype0011_01 (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    hire_date DATE NOT NULL,
    salary NUMERIC(10, 2) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES t_getxmltype0011(department_id)
);
INSERT INTO t_getxmltype0011 (department_name) VALUES
('HR'),
('Engineering'),
('Marketing'),
('Sales');
INSERT INTO t_getxmltype0011_01 (first_name, last_name, email, hire_date, salary, department_id) VALUES
('John', 'Doe', 'john.doe@example.com', '2023-01-01', 75000.00, 1),
('Jane', 'Smith', 'jane.smith@example.com', '2022-06-15', 80000.00, 2),
('Alice', 'Johnson', 'alice.johnson@example.com', '2021-11-22', 68000.00, 3),
('Bob', 'Brown', 'bob.brown@example.com', '2023-03-08', 72000.00, 4),
('Charlie', 'Davis', 'charlie.davis@example.com', '2022-09-10', 8500.25, 2);
--step2:查询语句为内连接;expect:成功
DECLARE
xml_cxt gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('SELECT e.first_name, e.last_name, d.department_name
FROM t_getxmltype0011_01 e
INNER JOIN t_getxmltype0011 d ON e.department_id = d.department_id',4564);
RAISE INFO 'INFO:XML Output: %', xml_type::text; 
gms_xmlgen.closecontext(xml_cxt);
END;
/
--step3:查询语句为左连接;expect:成功
DECLARE
xml_cxt gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('SELECT e.first_name, e.last_name, d.department_name
FROM t_getxmltype0011_01 e
LEFT JOIN t_getxmltype0011 d ON e.department_id = d.department_id;',4564);
RAISE INFO 'INFO:XML Output: %', xml_type::text; 
gms_xmlgen.closecontext(xml_cxt);
END;
/
--step4:带有条件的连接;expect:成功
DECLARE
xml_cxt gms_xmlgen.ctxhandle;
xml_type xmltype;
BEGIN
xml_type := gms_xmlgen.getxmltype('SELECT e.first_name, e.last_name, d.department_name
FROM t_getxmltype0011_01 e
INNER JOIN t_getxmltype0011 d ON e.department_id = d.department_id
WHERE e.salary > 70000',4564);
RAISE INFO 'INFO:XML Output: %', xml_type::text; 
gms_xmlgen.closecontext(xml_cxt);
END;
/
--step5:清理环境;expect:成功
drop table if exists t_getxmltype0011_01;
drop table if exists t_getxmltype0011;
drop extension if exists gms_xmlgen;